package com.miage.edt.base;

import com.miage.edt.bean.*;
import com.miage.util.sgbd.*;

import java.sql.*;
import java.util.*;

/**
 * Cette classe regroupe toutes les methodes qui vont chercher des infos dans la base et les retourne sous forme de beans
 */
@SuppressWarnings("unchecked")
public class SGBDConsultation extends SGBDAccesseur  
{    
    /** Creates a new instance of SGBDConsultation */
    public SGBDConsultation() {        
    }
    
    
    /**
     * Reccuperer information pour 1 s�ance
     */
    public static Seance consulterUneSeance(String id_seance) { 
        //Connexion � la base de donn�es.
        Connection conn = connexion();
        Statement requete = null;
        String strQuery = null;
          
        strQuery = "SELECT SE.SEA_SEANCE_ID,"  //1
                 + "nvl(SE.SAL_ID, 0),"         //2
                 + "nvl(SE.PER_ID_COURS, 0),"   //3
                 + "SE.EC_ID,"                  //4
                 + "SEM.PER_NUM_PERIODE,"       //5
                 + "nvl(EC.EC_DESIGNATION, ' '),"   //6
                 + "SE.PRO_LIBELLE,"                //7
                 + "nvl(SA.SAL_DESIGNATION, ' '),"  //8
                 + "to_char(SE.SEA_HEURE_DEB, 'HH24:MI'),"              //9
                 + "nvl(INT.INTV_NOM, ' '),"        //10
                 + "SE.SEA_DUREE,"                  //11
                 + "to_char(SE.JOU_DATE_JOUR, 'DD/MM/YYYY'),"   //12
                 + "to_char(SE.JOU_DATE_JOUR,'Day'),"            //13
                 + "SE.SEA_STATUT,"                             //14
                 + "SE.SEA_EXAM,"                               //15
                 + "to_char(SE.SEA_HEURE_MODIF,'HH24:MI'),"     //16
                 + "SE.EDT_ANNEE_UNIV,"                         //17
                 + "SE.UTI_LOGIN,"                              //18
                 + "SE.SEA_SEANCE_ID_MODIF"                    //19
                 + " FROM SEANCE SE,SALLE SA,INTERVENANT INT, EC,SEMAINE SEM "
                 + " WHERE  SE.SEA_SEANCE_ID = '" + id_seance + "'"
                 + " AND SE.SAL_ID = SA.SAL_ID (+)"
                 + " AND SE.EC_ID = EC.EC_ID"
                 + " AND SE.PER_ID_COURS = INT.PER_ID (+)"
                 + " AND SE.PRO_LIBELLE = SEM.PRO_LIBELLE"
                 + " AND SE.EDT_ANNEE_UNIV = SEM.EDT_ANNEE_UNIV"
                 + " AND SE.JOU_DATE_JOUR = SEM.JOU_DATE_JOUR";
        
        Seance uti = new Seance();
        
        try {
            requete = conn.createStatement();
            ResultSet rs = requete.executeQuery(strQuery);
            
            if (rs.next()) {
                uti.setIdSeance(rs.getInt(1));
                uti.setIdSalle(rs.getInt(2));
                uti.setIdIntervenant(rs.getInt(3));
                uti.setIdEC(rs.getInt(4));
                uti.setIntitule(rs.getString(6));
                uti.setPromotion(rs.getString(7));
                uti.setSalle(rs.getString(8));
                uti.setHeureDeb(rs.getString(9));
                uti.setProfesseur(rs.getString(10));
                uti.setDuree(rs.getFloat(11));
                uti.setDateSeance(rs.getString(12));
                uti.setJourSemaine(rs.getString(13));
                uti.setStatut(rs.getString(14));
                uti.setExam(rs.getString(15));
                uti.setLoginUtilisateur(rs.getString(16));
                uti.setModificationID(rs.getInt(19));
                uti.setHeureModif(rs.getString(18));
                uti.setAnneeUniv(rs.getString(17));
                
            } else uti = null; 

            //Fermeture des cursors
            rs.close();
            requete.close();
        
            //Deconnexion de la base de donn�es.
            deconnexion(conn);
            
        } catch (Exception e) {
            System.err.println(e);
            return null;
        }
        
        return uti;
     }

// Recuperer tous les intervenants -  pour consulter l'EDT d'un intervenant
// en sortie : la liste tri�e par nom avec le nom et le per_id

    public static Vector consulterListeIntervenant() {
        //Connexion � la base de donn�es.
        Connection conn = connexion();        
        Statement requete = null;
        String strQuery = null;

        strQuery = "select PER_ID, INTV_NOM, INTV_PRENOM  from intervenant order by intv_nom";
        Vector uti=new Vector();

        try {
            requete = conn.createStatement();
            ResultSet rs = requete.executeQuery(strQuery);            
            while (rs.next()) {
                Intervenant inter=new Intervenant();                
                inter.set_ID(rs.getInt(1));
                inter.set_Nom(rs.getString(2));
                inter.setPrenom(rs.getString(3));
                uti.add(inter);
            }                          
            //Fermeture des cursors
            rs.close();
            requete.close();

            //Deconnexion de la base de donn�es.
            deconnexion(conn);

        } catch (Exception e) {
            System.err.println(e);
            return null;
        }
        return uti;
    }

// recuperer tous les intervenants d'un EC identifi� par idEC
// pour modifier un intervenant et un intitul� et ajouter une seance
// entr�e : l'id de l'EC
// sortie : liste d'intervenants (vecteur de beans Intervenant) tri�e par nom

       
    public static Vector consulterListeIntervenant(int idEC) {
        //Connexion � la base de donn�es.        
        Connection conn = connexion();
        
        String strQuery = "SELECT i.per_id, i.intv_nom, i.intv_prenom "+
        "FROM ENSEIGNE E, intervenant i "+
        "WHERE E.EC_ID="+idEC+" and i.per_id = e.per_id "+
        "order by i.intv_nom";
        
        Statement requete = null;
        Vector uti=new Vector();
        
        try {
                requete = conn.createStatement();
                ResultSet rs = requete.executeQuery(strQuery);
                    
                while (rs.next()) 
                    {
                        Intervenant inter=new Intervenant();
                        inter.set_ID(rs.getInt(1));
                        inter.set_Nom(rs.getString(2));
                        inter.setPrenom(rs.getString(3));
                        uti.add(inter);
                    } 
                //Fermeture des cursors
                rs.close();
                requete.close();

            //Deconnexion de la base de donn�es.
            deconnexion(conn);
            
        } catch (Exception e) {
            System.err.println(e);
            return null;
        }               
        return uti;
    }
    
    
     /**
     * Cette fonction permet de savoir si un utilisateur existe dans la base
     * et si le mot de passe qu'on lui passe en parametre est correct
     * ca retourne un utilisateur si c'est correct, null sinon
     */
    public static Utilisateur consulterUtilisateur(String user, String pass) {
        
        //Connexion � la base de donn�es.
        Connection conn = connexion();
        
        String strQuery = null;
        Statement requete = null;
        
        //Mise � jour de la date de reponse du mail avec la date du jour
       strQuery = "select * from uti_edt where uti_login='"+user+"' and uti_pass='"+pass+"'";
        
       Utilisateur uti=new Utilisateur();
       
        try {
            requete = conn.createStatement();
            ResultSet rs = requete.executeQuery(strQuery);
            
            if (rs.next()) {
                uti.setNom(rs.getString(1));
                uti.setPass(rs.getString(2));
                uti.setDroit(rs.getString(3)); 
              
            } else { return null; }

           //Fermeture des cursors
            rs.close();
            requete.close();

            //Deconnexion de la base de donn�es.
            deconnexion(conn);
            
        } catch (Exception e) {
            System.err.println(e);
            return null;
        }
        return uti;
    }
    
	
// Recuperer toutes les seances d'une promotion - pour consulter l'EdT M1 ou l'EdT M2
// entree : la periode, la promotion et l'ann�e universitaire
// sortie : la liste des seances 
   

    public static Vector consulterListeSeancePromotion(String numPeriode, String promo, String anneeUniv)
	{
		Vector uti = new Vector();
		
		try {
                    Connection conn = connexion();
                    Statement requete = conn.createStatement();
		    String Query1 =  "SELECT SEA.SEA_EXAM,  SEA.SEA_SEANCE_ID, SEA.EC_ID, "+
                        "NVL(E.EC_DESIGNATION, ' ') AS ecDesignation, " +
                        "to_char(SEA.SEA_HEURE_DEB, 'HH24:MI') as heureDeb, (SEA.SEA_DUREE) as duree, " +
                        "NVL(SEA.SAL_ID, 0) AS saleId, NVL(SAL.SAL_DESIGNATION, ' ') as salleDesignation, "+
                        "NVL(SEA.PER_ID_COURS, 0) AS idIntervenant, " +
                        "NVL(INT.INTV_NOM, ' ') AS intNom, NVL(INT.INTV_PRENOM, ' ') AS intPrenom, " +
                        "to_char(SEA.JOU_DATE_JOUR, 'DD/MM/YYYY') AS dateSeance, SEM.SEM_NUM_SEMAINE, " +
                        "to_char(SEA.JOU_DATE_JOUR, 'Day') AS jourSemaine, "+
                        "to_char(SEA.JOU_DATE_JOUR, 'D') AS indiceJour, SEM.PER_NUM_PERIODE " +
                        "FROM EC E, SEANCE SEA, SALLE SAL, INTERVENANT INT, SEMAINE SEM " +
                        "WHERE  SEA.JOU_DATE_JOUR  = SEM.JOU_DATE_JOUR " +
                        "AND SEA.SEA_STATUT = 'V' " +
                        "AND SEA.EC_ID = E.EC_ID(+) " +
                        "AND SEA.pro_libelle = sem.pro_libelle " +
                        "AND SEM.PRO_LIBELLE = '"+promo+"' " +
                        "AND SEA.EDT_ANNEE_UNIV = '"+anneeUniv+"' " +
                        "AND SEA.SAL_ID = SAL.SAL_ID(+) " +
                        "AND SEA.PER_ID_COURS   = INT.PER_ID(+) " +
                        "ORDER BY SEA.JOU_DATE_JOUR, heureDeb";

			String Query2 = "SELECT SEA.SEA_EXAM, SEA.SEA_SEANCE_ID, SEA.EC_ID, "+
                        "NVL(E.EC_DESIGNATION, ' ') AS ecDesignation, " +
                        "to_char(SEA.SEA_HEURE_DEB, 'HH24:MI') as heureDeb, (SEA.SEA_DUREE) as duree, " +
                        "NVL(SEA.SAL_ID, 0) AS saleId, NVL(SAL.SAL_DESIGNATION, ' ') AS salleDesignation, "+
                        "NVL(SEA.PER_ID_COURS, 0) AS idIntervenant, " +
                        "NVL(INT.INTV_NOM, ' ') AS intNom, NVL(INT.INTV_PRENOM, ' ') AS intPrenom, " +
                        "to_char(SEA.JOU_DATE_JOUR, 'DD/MM/YYYY') AS dateSeance, " +
                        "SEM.SEM_NUM_SEMAINE, to_char(SEA.JOU_DATE_JOUR, 'Day') AS jourSemaine, "+
                        "to_char(SEA.JOU_DATE_JOUR, 'D') AS indiceJour, SEM.PER_NUM_PERIODE " +
                        "FROM EC E, SEANCE SEA, SALLE SAL, INTERVENANT INT,  SEMAINE SEM " +
                        "WHERE SEA.JOU_DATE_JOUR = SEM.JOU_DATE_JOUR " +
                        "AND SEM.PER_NUM_PERIODE = '"+numPeriode+"' " +
                        "AND SEA.SEA_STATUT ='V' " +
                        "AND SEA.EC_ID = E.EC_ID(+) " +
                        "AND SEA.pro_libelle = sem.pro_libelle " +
                        "AND SEM.PRO_LIBELLE = '"+promo+"' " +
                        "AND SEA.EDT_ANNEE_UNIV = '"+anneeUniv+"' " +
                        "AND SEA.SAL_ID = SAL.SAL_ID(+) " +
                        "AND SEA.PER_ID_COURS = INT.PER_ID(+) " +
                        "ORDER BY SEA.JOU_DATE_JOUR, heureDeb";
                    //System.out.println(Query1);
                    ResultSet rs;
                    if (numPeriode.equals("Tout"))
			rs  = requete.executeQuery(Query1);
                    else 
			rs = requete.executeQuery(Query2);
      
                    while (rs.next()) 
                            {		                        
                                Seance s = new Seance();
				s.setIdSeance(rs.getInt("SEA_SEANCE_ID"));
				s.setIdEC(rs.getInt("EC_ID"));
				s.setIdIntervenant(rs.getInt("idIntervenant"));
                                s.setIdSalle(rs.getInt("saleId"));
				s.setIdPeriode(rs.getInt("PER_NUM_PERIODE"));
				s.setSalleDesignation(rs.getString("salleDesignation"));
				s.setDateSeance(rs.getString("dateSeance"));
				s.setJourSemaine(rs.getString("jourSemaine"));
				s.setNumSemaine(rs.getInt("SEM_NUM_SEMAINE"));
//				s.setIndiceJour(rs.getInt("indiceJour")-1);	// Pour un Linux anglais, il faut faire -1 
				s.setIndiceJour(rs.getInt("indiceJour"));	// Pour un Linux fran�ais, OK
				s.setDuree(rs.getFloat("duree"));
				s.setHeureDeb(rs.getString("heureDeb"));
				s.setIntitule(rs.getString("ecDesignation"));
				s.setProfesseur(rs.getString("intNom"));
				s.setProfPrenom(rs.getString("intPrenom"));
                                //OUBLI !!!!!!
                                s.setAnneeUniv(anneeUniv);
				s.setPromotion(promo);
				s.setExam(rs.getString(1));
				uti.add(s);
                            }
                //Fermeture des cursors
                rs.close();
                requete.close();
            
                //Deconnexion de la base de donn�es.
                deconnexion(conn);
			
			}
			catch (Exception e) 
			{			
				System.err.println(e);
                                return null;
			}
		/*
                if (uti.isEmpty()) System.out.println("dans consulterListeSeancePromotion, lstSeance vide");
		else System.out.println("dans consulterListeSeancePromotion, lstSeance = "+uti.size()+" elements");
                */
                return uti;
		}
			
			
// Recuperer toutes les seances d'une salle - pour consulter l'EdT Salle
// entree : l'id de la salle et l'ann�e universitaire
// sortie : la liste des seances 
   
    public static Vector consulterListeSeanceSalle(int salle, String anneeUniv)
	{
		Vector uti = new Vector();
		try {
                        Connection conn = connexion();
                        Statement requete = conn.createStatement();
                        String req = "SELECT sea.pro_libelle, SEA.SEA_EXAM, SEA.SEA_SEANCE_ID, "+
                            "SEA.EC_ID, NVL(E.EC_DESIGNATION, ' ') As ecDesignation, " +
                            "to_char(SEA.SEA_HEURE_DEB, 'HH24:MI') AS heureDeb, "+
                            "(SEA.SEA_DUREE) as duree, " +
                            "NVL(SEA.SAL_ID, 0) AS saleId, SA.SAL_DESIGNATION, "+
                            "NVL(SEA.PER_ID_COURS, 0) AS idIntervenant, " +
                            "NVL(INT.INTV_NOM, ' ') AS intNom, NVL(INT.INTV_PRENOM, ' ') AS intPrenom, "+
                            "to_char(SEA.JOU_DATE_JOUR, 'DD/MM/YYYY') AS dateSeance, "+
                            "to_char(SEA.JOU_DATE_JOUR, 'D') AS indiceJour, SEM.SEM_NUM_SEMAINE, "+
                            "to_char(SEA.JOU_DATE_JOUR, 'Day') AS jourSemaine, SEM.PER_NUM_PERIODE " +
                            "FROM EC E, SEANCE SEA, SALLE SA, INTERVENANT INT, SEMAINE SEM " +
                            "WHERE SEA.EDT_ANNEE_UNIV = '"+anneeUniv+"' " +
                            "AND SEA.SAL_ID = "+salle+" " +
                            "AND NVL(SEA.SAL_ID, 0) = SA.SAL_ID(+) " +
                            "AND NVL(SEA.PER_ID_COURS, 0) = INT.PER_ID(+) " +
                            "AND SEA.EC_ID = E.EC_ID " +
                            "AND SEA.SEA_STATUT ='V' " +
                            "AND SEA.pro_libelle = sem.pro_libelle " +
                            "AND SEA.JOU_DATE_JOUR = SEM.JOU_DATE_JOUR " +
                            "ORDER BY SEA.JOU_DATE_JOUR, heureDeb";
						
                        ResultSet rs = requete.executeQuery(req);
                        while (rs.next()) 
                            {
                                Seance s = new Seance();
				s.setPromotion(rs.getString("pro_libelle"));
				s.setIdSeance(rs.getInt("SEA_SEANCE_ID"));
				s.setIdEC(rs.getInt("EC_ID"));
				s.setIdIntervenant(rs.getInt("idIntervenant"));
				s.setIdSalle(rs.getInt("saleId"));
				s.setIdPeriode(rs.getInt("PER_NUM_PERIODE"));
				s.setSalleDesignation(rs.getString("SAL_DESIGNATION"));
				s.setDateSeance(rs.getString("dateSeance"));
				s.setJourSemaine(rs.getString("jourSemaine"));
				s.setNumSemaine(rs.getInt("SEM_NUM_SEMAINE"));
				s.setIndiceJour(rs.getInt("indiceJour"));
				s.setDuree(rs.getFloat("duree"));
				s.setHeureDeb(rs.getString("heureDeb"));
				s.setIntitule(rs.getString("ecDesignation"));
				s.setProfesseur(rs.getString("intNom"));
				s.setProfPrenom(rs.getString("intPrenom"));
				s.setExam(rs.getString("sea_exam"));
                                ///////OUBLI
                                s.setAnneeUniv(anneeUniv);
				uti.add(s);
                            }
                    //Fermeture des cursors
                    rs.close();
                    requete.close();
            
                    //Deconnexion de la base de donn�es.
                    deconnexion(conn);
            
                    } catch (Exception e) {
                        System.err.println(e);
                        return null;
                    }
        return uti;			
	}
	
// Recuperer toutes les seances d'un EC - pour consulter l'EdT EC
// entree : l'id de l'EC et l'ann�e universitaire
// sortie : la liste des seances 
   
    public static Vector consulterListeSeanceEC(int idEC, String anneeUniv)
	{
		Vector uti = new Vector();
		try {
			Connection conn = connexion();
			Statement requete = conn.createStatement();
			ResultSet rs = requete.executeQuery("SELECT se.pro_libelle, SE.SEA_EXAM,  EC_Designation, "+
                            "TO_CHAR(SEA_HEURE_DEB,'HH24:MI') AS heureDeb, " +
                            "((SE.SEA_DUREE)) as duree, SAL.SAL_DESIGNATION, INT.INTV_NOM, "+
                            "INT.INTV_PRENOM, TO_CHAR(SE.JOU_DATE_JOUR,'DD/MM/YYYY') AS dateSeance, "+
                            "to_char(SE.JOU_DATE_JOUR, 'D') AS indiceJour, SEM.SEM_NUM_SEMAINE, "+
                            "TO_CHAR(SE.JOU_DATE_JOUR,'Day') AS jourSemaine, " +
                            "SEM.PER_NUM_PERIODE, SE.SEA_SEANCE_ID, "+
                            "NVL(SE.PER_ID_COURS,'0') AS idIntervenant, SE.SAL_ID, SE.EC_ID " +
                            "FROM SEANCE SE, INTERVENANT INT, SALLE SAL, SEMAINE SEM, ec " +
                            "WHERE ec.EC_ID="+idEC+" AND ec.EC_ID = SE.EC_ID AND SE.SAL_ID = SAL.SAL_ID "+
                            "AND SE.PER_ID_COURS = INT.PER_ID AND SE.EDT_ANNEE_UNIV = '" + anneeUniv + "' " +
                            "AND SEM.JOU_DATE_JOUR = SE.JOU_DATE_JOUR " +
                            "and sem.pro_libelle = se.pro_libelle "+
                            "AND SE.SEA_STATUT ='V' " +
                            "ORDER BY SE.JOU_DATE_JOUR, heureDeb");
						
                        while (rs.next()) 
                            {
                                Seance s = new Seance();
                                s.setIdSeance(rs.getInt("SEA_SEANCE_ID"));
				s.setIdEC(rs.getInt("EC_ID"));
				s.setIdIntervenant(rs.getInt("idIntervenant"));
				s.setIdSalle(rs.getInt("SAL_ID"));
				s.setIdPeriode(rs.getInt("PER_NUM_PERIODE"));
				s.setSalleDesignation(rs.getString("SAL_DESIGNATION"));
				s.setDateSeance(rs.getString("dateSeance"));
				s.setJourSemaine(rs.getString("jourSemaine"));
				s.setNumSemaine(rs.getInt("SEM_NUM_SEMAINE"));
				s.setIndiceJour(rs.getInt("indiceJour"));
				s.setDuree(rs.getFloat("duree"));
				s.setHeureDeb(rs.getString("heureDeb"));
				s.setIntitule(rs.getString("EC_Designation"));
				s.setProfesseur(rs.getString("INTV_NOM"));
				s.setProfPrenom(rs.getString("INTV_PRENOM"));
                                s.setPromotion(rs.getString("pro_libelle"));
				s.setExam(rs.getString("SEA_EXAM"));
                                ///// OUBLI
                                s.setAnneeUniv(anneeUniv);
				uti.add(s);
                            }
                    //Fermeture des cursors
                    rs.close();
                    requete.close();
            
                    //Deconnexion de la base de donn�es.
                    deconnexion(conn);
                    
                    } catch (Exception e) {
                        System.err.println(e);
                        return null;
                    }		         
                return uti;
	}
	
// Recuperer toutes les seances d'un intervenant - pour consulter l'EdT Intervenant
// entree : le per_id de l'intervenant et l'ann�e universitaire
// sortie : la liste des seances
   
    public static Vector consulterListeSeanceIntervenant(String idInter, String anneeUniv)
	{

            Vector uti = new Vector();
		
            try {
                    Connection conn = connexion();
                    Statement requete = conn.createStatement();
                    ResultSet rs = requete.executeQuery("SELECT SE.SEA_EXAM, EC.EC_DESIGNATION, "+
                        "TO_CHAR(SE.SEA_HEURE_DEB,'HH24:MI') as heureDeb, " +
                        "(SE.SEA_DUREE) as duree, SA.SAL_DESIGNATION, INT.INTV_NOM, INT.INTV_PRENOM," +
                        "TO_CHAR(SE.JOU_DATE_JOUR,'DD/MM/YYYY') AS dateSeance, "+
                        "TO_CHAR(SE.JOU_DATE_JOUR, 'Day') AS jourSemaine, "+
                        "to_char(SE.JOU_DATE_JOUR, 'D') AS indiceJour, " +
                        "SEM.SEM_NUM_SEMAINE, SEM.PER_NUM_PERIODE, SE.SEA_SEANCE_ID, SE.PRO_LIBELLE, " +
                        "SE.PER_ID_COURS, SE.SAL_ID, SE.EC_ID " +
                        "FROM SEANCE SE, EC, INTERVENANT INT, SALLE SA, SEMAINE SEM, JOUR JO " +
                        "WHERE SE.PER_ID_COURS = '"+idInter+"' AND EC.EC_ID = SE.EC_ID " +
                        "and sem.pro_libelle = se.pro_libelle and sem.jou_date_jour = se.jou_date_jour "+
                        "AND INT.PER_ID = SE.PER_ID_COURS AND SA.SAL_ID (+) = SE.SAL_ID "+
                        "AND JO.JOU_DATE_JOUR = SE.JOU_DATE_JOUR AND SEM.JOU_DATE_JOUR = JO.JOU_DATE_JOUR " +
                        "AND SE.SEA_STATUT ='V' " +
                        "AND SE.EDT_ANNEE_UNIV = '"+anneeUniv+"' "+
                        "ORDER BY SE.JOU_DATE_JOUR, heureDeb");
	
                    while (rs.next()) 
                        {
                            Seance s = new Seance();
                            s.setIdSeance(rs.getInt("SEA_SEANCE_ID"));
                            s.setIdEC(rs.getInt("EC_ID"));
                            s.setIdIntervenant(rs.getInt("PER_ID_COURS"));
                            s.setIdSalle(rs.getInt("SAL_ID"));
                            s.setIdPeriode(rs.getInt("PER_NUM_PERIODE"));
                            s.setSalleDesignation(rs.getString("SAL_DESIGNATION"));
                            s.setDateSeance(rs.getString("dateSeance"));
                            s.setJourSemaine(rs.getString("jourSemaine"));
                            s.setNumSemaine(rs.getInt("SEM_NUM_SEMAINE"));
                            s.setIndiceJour(rs.getInt("indiceJour"));
                            s.setDuree(rs.getFloat("duree"));
                            s.setHeureDeb(rs.getString("heureDeb"));
                            s.setIntitule(rs.getString("EC_DESIGNATION"));
                            s.setProfesseur(rs.getString("INTV_NOM"));
				s.setProfPrenom(rs.getString("INTV_PRENOM"));
                            s.setPromotion(rs.getString("PRO_LIBELLE"));
                            /// OUBLI
                            s.setAnneeUniv(anneeUniv);
			s.setExam(rs.getString(1));	
                            uti.add(s);
                        }
                //Fermeture des cursors
                    rs.close();
                    requete.close();
            
                //Deconnexion de la base de donn�es.
                    deconnexion(conn);
            
                } catch (Exception e) {
                    System.err.println(e);
                    return null;
			}
            return uti;		
	}
        
//Reccuperer toutes les salles - Pour ajouter une seance ou modifier la salle d'une seance

    public static Vector consulterListeSalle() 
    { 
        Vector uti = new Vector();
 
        try {
            Connection conn = connexion();
            Statement requete = conn.createStatement();
            ResultSet rs = requete.executeQuery("SELECT SAL_ID, SAL_DESIGNATION FROM SALLE ORDER BY SAL_ID");
            while (rs.next()) {
                Salle s = new Salle();
                s.setIdSalle(rs.getInt("SAL_ID"));
                s.setLibelle(rs.getString("SAL_DESIGNATION"));                                
                uti.add(s);                              
            }
            //Fermeture des cursors
            rs.close();
            requete.close();

            //Deconnexion de la base de donn�es.
            deconnexion(conn);

        } catch (Exception e) {
            System.err.println(e);
            return null;
        }
        return uti;
    }

//Reccuperer tous les EC - pour consulter l'EdT par EC ou pour modifier l'EC d'une seance
	
    public static Vector consulterListeEC()
    {
        Vector uti = new Vector();
		
        try {
            Connection conn = connexion();
            Statement requete = conn.createStatement();
            ResultSet rs = requete.executeQuery("SELECT DISTINCT ec.ec_id, EC.EC_DESIGNATION, ec.pro_libelle " +
												"FROM EC ORDER BY  ec.pro_libelle, EC.EC_DESIGNATION");
			while (rs.next()) 
                {
                    EC ec = new EC();
                    ec.setIdEC(rs.getInt(1));
                    ec.setLibelleEC(rs.getString(2));
                    ec.setPromotion(rs.getString(3));
                    uti.add(ec);
                }
                        
            //Fermeture des cursors
            rs.close();
            requete.close();
            //Deconnexion de la base de donn�es.
            deconnexion(conn);
            } catch (Exception e) {
                System.err.println(e);
                return null;
            }		         
        return uti;	
    }
	
	//Recuperer tous les EC d'une promotion
	
    public static Vector consulterListeEC(String promotion)
    {
        Vector uti = new Vector();
		
        try {
            Connection conn = connexion();
            Statement requete = conn.createStatement();
            ResultSet rs = requete.executeQuery("SELECT DISTINCT ec.ec_id, EC.EC_DESIGNATION, ec.pro_libelle " +
												"FROM EC where ec.pro_libelle = '"+promotion+"' ORDER BY  ec.pro_libelle, EC.EC_DESIGNATION");
			while (rs.next()) 
			{
				EC ec = new EC();
				ec.setIdEC(rs.getInt(1));
				ec.setLibelleEC(rs.getString(2));
				ec.setPromotion(rs.getString(3));
				uti.add(ec);
			}
			
            //Fermeture des cursors
            rs.close();
            requete.close();
            //Deconnexion de la base de donn�es.
            deconnexion(conn);
		} catch (Exception e) {
			System.err.println(e);
			return null;
		}		         
        return uti;	
    }

	//Recuperer toutes les periodes d'une promotion - pour consulter l'EdT d'une promotion

    public static Vector consulterListePeriodePromotion(String promo, String anneeUniv)
    {
        //System.out.println("promo = "+promo);
	//System.out.println("anneeUniv = "+anneeUniv);
	Vector uti = new Vector();
            
        try {
            Connection conn = connexion();
            Statement requete = conn.createStatement();
            ResultSet rs = requete.executeQuery("SELECT DISTINCT PER_NUM_PERIODE " +
                    "FROM SEMAINE " +
                    "WHERE PRO_LIBELLE = '"+promo+"' " +
                    "AND EDT_ANNEE_UNIV = '"+anneeUniv+"' " +
                    "ORDER BY PER_NUM_PERIODE");
            while (rs.next()) 
                uti.add(rs.getString("PER_NUM_PERIODE"));

        //Fermeture des cursors
        rs.close();
        requete.close();
        //Deconnexion de la base de donn�es.
        deconnexion(conn);
            
        } catch (Exception e) {
            System.err.println(e);
            return null;
            }
        return uti;
    }
		
//Reccuperer toutes les annes universitaires - pour consulter l'EdT

    public static Vector consulterListeAnneeUniv()
    {
        Vector uti = new Vector();
        try {
            Connection conn = connexion();
            Statement requete = conn.createStatement();
            ResultSet rs = requete.executeQuery("SELECT DISTINCT(EDT_ANNEE_UNIV) " +
                    "FROM EDT " +
                    "ORDER BY EDT_ANNEE_UNIV DESC");
            while (rs.next()) 
                uti.add(rs.getString("EDT_ANNEE_UNIV"));
                        
            //Fermeture des cursors
            rs.close();
            requete.close();
            //Deconnexion de la base de donn�es.
            deconnexion(conn);
        } catch (Exception e) {
                System.err.println(e);
                return null;
            }
        return uti;
    }


public static EC consulterEC(int idEC) {

EC ec = new EC(); 
      try {
        Connection conn = connexion();
        String strQuery = "select EC_DESIGNATION, pro_libelle from EC where EC_ID ="+idEC;
        Statement requete = conn.createStatement();
        ResultSet rs = requete.executeQuery(strQuery);
        if (rs.next()) {
            ec.setLibelleEC(rs.getString(1));
	    ec.setPromotion(rs.getString(2));
	    ec.setIdEC(idEC);
        }
        rs.close();
        requete.close();
        deconnexion(conn);
         } catch (Exception e) {
            System.err.println(e);
		return null;
        }
        return ec;
    }

    //TODO
    public static Vector consulterListePromo() {
        return null;
    }

	/* methode de consultation des intervenants 
	 copi�e et adapt�e de GAUMIAGE par MPG le 14 mars 2011 */
	
    public static Vector consulterListeIntervenants(String nom, String prenom, String statut) {
        //Connexion � la base de donn�es.
        Connection conn = connexion();
        String strQuery = null;
        Statement requete = null;
		
        strQuery = "select  distinct per_id, intv_nom, intv_prenom "
		+" from intervenant where intv_statut='"+statut+"'";
		
		if (nom != null && !nom.trim().equals("")) { strQuery += " AND intv_nom='"+nom+"'"; }
		if (prenom != null && !prenom.trim().equals("")) { strQuery += " AND intv_prenom='"+prenom+"'"; }
		
		strQuery += " order by intv_nom";   
		System.out.println(strQuery);
        Vector uti = new Vector();
        
        try {
            requete = conn.createStatement();
            ResultSet rs = requete.executeQuery(strQuery);
            
            while (rs.next()) {
                Intervenant a = new Intervenant();
                a.set_ID(rs.getInt(1));
                a.set_Nom(rs.getString(2));
                a.setPrenom(rs.getString(3));
                a.setStatut(statut);
                uti.add(a);
            }
            //Fermeture des cursors
            requete.close();
            
            rs.close();
            //Deconnexion de la base de donn�es.
            deconnexion(conn);
            
        } catch (Exception e) {
            System.err.println(e);
            return null;
        }
        
        return uti;        
	}

	/* methode de consultation d'un intervenant 
	 copi�e et adapt�e de GAUMIAGE par MPG le 14 mars 2011 */

	public static Intervenant consulterSignaletiqueIntervenant(int identifiant) {
        //Connexion � la base de donn�es.
        Connection conn = connexion();
        String strQuery = null;
		Statement requete = null;
        
		strQuery="select per_id, intv_nom, intv_prenom, intv_tel, intv_tel_autre, intv_email, intv_statut  from intervenant where per_id="+identifiant;
		
        Intervenant uti= new Intervenant();
        try {
            requete = conn.createStatement();
			ResultSet rs = requete.executeQuery(strQuery);	           
            if (rs.next()) 
				{                
				uti.set_ID(rs.getInt(1));
                uti.set_Nom(rs.getString(2));
                uti.setPrenom(rs.getString(3));
				uti.setTel(rs.getString(4));
				uti.setTel_autre(rs.getString(5));
				uti.setEmail(rs.getString(6));		
               uti.setStatut(rs.getString(7));		
				uti.setMatieres(consulterMatieresEnseignees(identifiant));
				}
			else { System.out.println("on renvoie null"); return null; }
            //Fermeture des cursors
            requete.close();
           rs.close();
            //Deconnexion de la base de donn�es.
            deconnexion(conn);
            
        } catch (Exception e) {
            System.err.println(e);
            return null;
        }
        return uti;
    }
    	

	/**
     * Retourne les EC enseign�s par un intervenant
     */
    public static Vector consulterMatieresEnseignees(int id) {
        Connection conn = connexion();
        String strQuery = null;
        Statement requete = null;
        Vector uti= new Vector();
        
        strQuery = "select distinct ec.ec_id, ec_designation, pro_libelle from enseigne e, ec where ec.ec_id = e.ec_id and e.per_id ="+id+" order by pro_libelle";
		
        try {
            requete = conn.createStatement();
            ResultSet rs = requete.executeQuery(strQuery);
            
            while (rs.next()) {
				EC c = new EC();
                c.setIdEC(rs.getInt(1));
                c.setLibelleEC(rs.getString(2));
                c.setPromotion(rs.getString(3));
                uti.add(c);                
            } 
            //Fermeture des cursors
            requete.close();
            
            rs.close();
            //Deconnexion de la base de donn�es.
            deconnexion(conn);
            
        } catch (Exception e) {
            System.err.println(e);
            return null;
        }
		return uti;       
    }

	
	/* methode de modification des intervenants 
	 copi�e et adapt�e de GAUMIAGE par MPG le 14 mars 2011 */
	
	public static int modifierSignaletiqueIntervenant(
													  int identifiantIntervenant,
													  String int_statut,
													  String int_tel,
													  String int_portable,
													  String int_email,
													  String[] nouvelles_matieres_enseignees,
													  String[] supp_matieres_enseignees) {
        
        int retour = 0;
        String strQuery="update intervenant set intv_tel ='"+int_tel+"',";
		strQuery += "  intv_tel_autre ='"+int_portable+"', intv_statut = '"+int_statut+"'," ;
        strQuery += " intv_email = '"+int_email+"' where per_id = "+ identifiantIntervenant;
        System.out.println(strQuery);
        retour = update(strQuery);
		
       //Ajout de toutes les matieres enseign�es s'il y en a
        String query5="";
        
		if (nouvelles_matieres_enseignees != null)
			for(int i=0;i<nouvelles_matieres_enseignees.length;i++){
				query5 = "insert into enseigne (per_id, ec_id) values ("+
                identifiantIntervenant+", "+nouvelles_matieres_enseignees[i]+")";
            retour = update(query5);};
		
        //Suppression de matieres enseign�es s'il y en a
        String query6="";
        
		if (supp_matieres_enseignees != null)
			for(int i=0;i<supp_matieres_enseignees.length;i++){
				query6 = "delete from enseigne where per_id ="+
                identifiantIntervenant+" and ec_id="+supp_matieres_enseignees[i];
            retour = update(query6);};
		
         return retour;
    }
	
}






